﻿using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace HelloWorldBll
{
    public class AccessDb
    {
        private readonly string _myConnString = ConfigurationManager.ConnectionStrings["yijianso"].ConnectionString;
        private OleDbConnection _conn;
        public OleDbConnection conn
        {
            get
            {
                if (_conn == null)
                    _conn = new OleDbConnection(_myConnString);
                return _conn;
            }
        }
        private OleDbCommand _comm;
        public OleDbCommand comm
        {
            get
            {
                if (_comm == null)
                {
                    _comm = new OleDbCommand();
                    _comm.Connection = conn;
                }
                return _comm;
            }
        }
        private void Openconn()
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        }
        private void Closeconn()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }


        /// <summary> 
        /// 执行Sql查询语句 (增删改)
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public int ExecuteSql(string sqlstr)
        {
            try
            {
                Openconn();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                return comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                Closeconn();
            }
        }


        /// <summary> 
        /// 执行存储过程 
        /// </summary> 
        /// <param name="procName">存储过程名</param> 
        /// <param name="coll">SqlParameters 集合</param> 
        public void ExecutePorcedure(string procName, SqlParameter[] coll)
        {
            try
            {
                Openconn();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Parameters.Clear();
                Closeconn();
            }
        }

        /// <summary> 
        /// 执行存储过程并返回数据集 
        /// </summary> 
        /// <param name="procName">存储过程名称</param> 
        /// <param name="coll">SqlParameter集合</param> 
        /// <param name="ds">DataSet </param> 
        public void ExecutePorcedure(string procName, SqlParameter[] coll, ref DataSet ds)
        {
            try
            {
                var da = new OleDbDataAdapter();
                Openconn();
                for (int i = 0; i < coll.Length; i++)
                {
                    comm.Parameters.Add(coll[i]);
                }
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;

                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                comm.Parameters.Clear();
                Closeconn();
            }
        }

        /// <summary> 
        /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>object 返回值 </returns> 
        public object ExecuteScalar(string sqlstr)
        {
            object obj;
            try
            {
                Openconn();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                obj = comm.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                Closeconn();
            }
            return obj;
        }

        /// <summary> 
        /// 执行Sql查询语句,同时进行事务处理 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        public void ExecuteSqlWithTransaction(string sqlstr)
        {
            OleDbTransaction trans;
            trans = conn.BeginTransaction();
            comm.Transaction = trans;
            try
            {
                Openconn();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                comm.ExecuteNonQuery();
                trans.Commit();
            }
            catch
            {
                trans.Rollback();
            }
            finally
            {
                Closeconn();
            }
        }
        /// <summary> 
        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接 
        /// 方法关闭数据库连接 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <param name="dr">传入的ref DataReader 对象</param> 
        public void DataReader(string sqlstr, ref OleDbDataReader dr)
        {
            try
            {
                Openconn();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    if (dr != null && !dr.IsClosed)
                        dr.Close();
                }
                catch
                {
                }
                finally
                {
                    Closeconn();
                }
            }
        }

        /// <summary> 
        /// 返回指定Sql语句的DataTable 
        /// </summary> 
        /// <param name="sqlstr">传入的Sql语句</param> 
        /// <returns>DataTable</returns> 
        public DataTable GetdataTableBySql(string sqlstr)
        {
            var da = new OleDbDataAdapter();
            var datatable = new DataTable();
            try
            {
                Openconn();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception e)
            {
                var err = new Err();
                err.writeInLog(e.Message);
            }
            finally
            {
                Closeconn();
            }
            return datatable;
        }
        /// <summary> 
        /// 执行带参数存储过程并返回数据集合 
        /// </summary> 
        /// <param name="procName">存储过程名称</param> 
        /// <param name="parameters">SqlParameterCollection 输入参数</param> 
        /// <returns></returns> 
        public DataTable GetdataTableByProc(string procName, OleDbParameterCollection parameters)
        {
            var da = new OleDbDataAdapter();
            var datatable = new DataTable();
            try
            {
                Openconn();
                comm.Parameters.Clear();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = procName;
                foreach (OleDbParameter para in parameters)
                {
                    var p = para;
                    comm.Parameters.Add(p);
                }
                da.SelectCommand = comm;
                da.Fill(datatable);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                Closeconn();
            }
            return datatable;
        }

    }
}
